 with tmsnew_tms_vehicle_line as 
(
        select * from (
            select 
             t.name
            ,t.start_code    
            ,t.end_code
            ,t.planned_arrival_time   --到车时间
            ,t.give_shifts_time
            ,row_number()over(partition by t.start_name  ,t.end_name order by t.planned_arrival_time desc ) as rn
            from jms_dim.dim_yl_tmsnew_tms_vehicle_line_base_dt t
            where t.dt  = '{{ execution_date  | cst_ds }}'
                  and t.is_enable='1'
        ) t where t.rn =1
)

,dm_transit_volume_monitor_sum_day_dt_1 as (
        select  
                   t.center_code_1                                              --始发中心
                  ,t.center_code_2                                              --中转中心
                  ,t.center_code_3                                              --目的中心
                  ,t.center_name_1
                  ,t.center_name_2                                              --中转中心
                  ,t.center_name_3                                              --目的中心
                  ,sum(t.bill_cnt)*1.0000 as bill_cnt                           --平均票数
                  ,sum(t.package_total_volume) as package_total_volume          --总体积
                  ,t.dt
        from  jms_dm.dm_transit_volume_monitor_sum_day_dt t
        left join jms_dim.dim_network_whole_massage t4 on t.center_code_2=t4.code
        left join jms_dim.dim_network_whole_massage t5 on t.center_code_3=t5.code
        where t.dt  between date_add('{{ execution_date  | cst_ds }}', -14) and date_add('{{ execution_date  | cst_ds }}', -1)
                    and size(split(t.real_line_code,'-') )=3
                    and  t4.agent_code in (130000,120000,110000) 
                    and  t5.agent_code  in (130000,120000,110000) 
                    and cnt_type='transit'
                    and sender_center_code=center_code_1
                    and receiver_center_code=center_code_3                    
        group by    t.center_code_1 
                   ,t.center_code_2
                   ,t.center_code_3
                   ,t.center_name_1
                   ,t.center_name_2                                              --中转中心
                   ,t.center_name_3 
                   ,t.dt   
        having sum(t.package_total_volume) >30           
                   
)
,dm_transit_volume_monitor_sum_day_dt_2 as (

select             
   t.center_code_1                                              --始发中心
  ,t.center_code_2                                              --中转中心
  ,t.center_code_3                                              --目的中心
  ,t.center_name_1
  ,t.center_name_2                                              --中转中心
  ,t.center_name_3                                              --目的中心
  ,sum(t.bill_cnt)*1.0000/COUNT(dt) as bill_cnt                           --平均票数
  ,sum(t.package_total_volume)*1.0000 as package_total_volume          --总体积
  ,sum(t.package_total_volume)*1.0000/COUNT(dt) as avg_package_total_volume          --总体积
from dm_transit_volume_monitor_sum_day_dt_1 t
group by    t.center_code_1
   ,t.center_code_2
   ,t.center_code_3
   ,t.center_name_1
   ,t.center_name_2                                              --中转中心
   ,t.center_name_3 
)

,transit_volume_monitor as (
        select 
           t.center_code_1                                              --始发中心
          ,t.center_code_2                                              --中转中心
          ,t.center_code_3                                              --目的中心
          ,t.center_name_1
          ,t.center_name_2                                              --中转中心
          ,t.center_name_3                                              --目的中心
          ,t.bill_cnt as bill_cnt                        --平均票数
          ,t.package_total_volume as package_total_volume          --总体积
          ,t.avg_package_total_volume as avg_package_total_volume  --平均体积

          ,t1.planned_arrival_time  as planned_arrival_time                                  --始发-目的公里数
          ,t1.give_shifts_time as give_shifts_time   
        from dm_transit_volume_monitor_sum_day_dt_2 t 
        left join tmsnew_tms_vehicle_line     t1 on t.center_code_1=t1.start_code and t.center_code_2=t1.end_code
         where avg_package_total_volume <=60                                                

)
,shipment_stop_billcode_detail_dt1 as (
     select 
      shipmentname      --线路名称
     ,senderdtributecode   --始发中心
     ,receiverdtributecode --目的中心
     ,vehicletypename
     ,sum(nvl(linepartcount,0)+nvl(false_num,0))*1.00 as bill_cnt
     ,to_date(app_arrival_time) as dt
     from jms_dm.dm_tms_trunk_load_factor_basic_dt t 
     where t.dt  between date_add('{{ execution_date  | cst_ds }}', -30) and '{{ execution_date  | cst_ds }}'
             and t.app_arrival_time  between concat(date_add('{{ execution_date  | cst_ds }}', -14),' 14:00:00') and concat(date_add('{{ execution_date  | cst_ds }}', -1),' 13:59:00')
            and size(split(t.shipmentname,'-') )=2
    group by 
          shipmentname      --线路名称
         ,senderdtributecode   --始发中心
         ,receiverdtributecode --目的中心
         ,vehicletypename
         ,to_date(app_arrival_time) 
)
,shipment_stop_billcode_detail_dt as (

     select 
      shipmentname      --线路名称
     ,senderdtributecode   --始发中心
     ,receiverdtributecode --目的中心
     ,vehicletypename
     ,sum(bill_cnt)*1.00/count(distinct dt) as bill_cnt
     from shipment_stop_billcode_detail_dt1
    group by 
          shipmentname      --线路名称
         ,senderdtributecode   --始发中心
         ,receiverdtributecode --目的中心
         ,vehicletypename
)
,tmp_res_1 as (
    select
           t.center_code_1                   --始发中心
          ,t.center_code_2                   --中转中心
          ,t.center_code_3                   --目的中心
          ,t.center_name_1                   --始发中心
          ,t.center_name_2                   --中转中心
          ,t.center_name_3                   --目的中心
          ,t.bill_cnt                        --平均票数
          ,t.package_total_volume            --总体积
          ,t.avg_package_total_volume  --平均体积
          ,t.planned_arrival_time                                  --始发-目的公里数
          ,t.give_shifts_time   
          
          ,unix_timestamp(concat('{{ execution_date  | cst_ds }} ',t.planned_arrival_time,':00'), 'yyyy-MM-dd HH:mm:ss')+ 3.5*60*60  as s_planned_departure_time1
          ,unix_timestamp(concat('{{ execution_date  | cst_ds }} ',t.give_shifts_time,':00'), 'yyyy-MM-dd HH:mm:ss')  as s_planned_departure_time2
          ,t1.bill_cnt as tt_bill_cnt
          ,t1.shipmentname
          ,t1.vehicletypename
          
          ,case when t1.bill_cnt=0 then 0 
                    when t.bill_cnt/t1.bill_cnt>=0.3 and t.bill_cnt/t1.bill_cnt<=0.6 then t.bill_cnt/t1.bill_cnt  end  as bill_rate
    from transit_volume_monitor t
    left join shipment_stop_billcode_detail_dt t1 on t.center_code_1=t1.senderdtributecode and t.center_code_2=t1.receiverdtributecode
)
insert overwrite table jms_dm.dm_enough_goods_detail_dt partition (dt)
select 
   t.center_code_1                   --始发中心
  ,t.center_code_2                   --中转中心
  ,t.center_code_3                   --目的中心
  ,t.center_name_1                   --始发中心name
  ,t.center_name_2                   --中转中心name
  ,t.center_name_3                   --目的中心name
  ,round(t.bill_cnt ,4)  bill_cnt                     --平均票数
  ,round(t.package_total_volume,4)  package_total_volume          --总体积
  ,round(t.avg_package_total_volume,4) avg_package_total_volume       --平均体积
  ,t.planned_arrival_time            --中转中心规划到达时间
  ,t.give_shifts_time                --中转中心最早散货支线时间
  ,round(t.tt_bill_cnt,4) tt_bill_cnt                    --始发-中转票数
  ,t.shipmentname                    --线路名称
  ,t.vehicletypename                 --车型
  ,round(t.bill_rate,4) bill_rate                      --占比
  ,case when from_unixtime(t.s_planned_departure_time1,'HH:mm')<=from_unixtime(t.s_planned_departure_time2,'HH:mm')  then 1 else 0 end as is_optimize --是否优化1是0否
  ,concat(date_add('{{ execution_date  | cst_ds }}', -14),'-',date_add('{{ execution_date  | cst_ds }}', -1))  as run_date --取值范围
  ,date_add('{{ execution_date  | cst_ds }}', -1) as date_time --date_time
  ,date_add('{{ execution_date  | cst_ds }}', -1) as dt        --dt
from  tmp_res_1 t 
where t.bill_rate >=0.3 and t.bill_rate<=0.6
 distribute by pmod(hash(rand()), 2)
;
